﻿using Microsoft.Data.Sqlite;
using MsBox.Avalonia.Enums;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace 畜牧业审核程序国产.Models
{
    internal class ReplaceText
    {
        //MyDBOperator replaceOperator = new MyDBOperator();改用单例模式
        MyDBOperator replaceOperator = MyDBOperator.DBOperatorInstance;
        static string  _dbPath = Path.Combine(MyDataFolderOperator.AuditProgramFolderPath, "畜牧业数据库.db");
        
        public static async void ReplaceSomeText()
        {
            string connectionString = $"Data Source={_dbPath}";
            var replacements = new (string oldText, string newText)[]
            {
//("25公斤以下仔猪02", "25公斤以下仔猪存栏数02"),
//( "待育肥猪03", "待育肥猪存栏数03"),
//( "50公斤以上04", "50公斤以上待育肥猪存栏数04"),
//("种猪05", "种猪存栏数05"),
//("能繁殖母猪06", "能繁殖母猪存栏数06"),
//("自繁08", "自繁头数08"),
//("购进09", "购进头数09"),
//("出售肥猪金额13", "出售肥猪金额（元）13"),
//("出售肥猪重量14", "出售肥猪重量（公斤）14"),
//("出售25公斤以下仔猪金额17", "出售25公斤以下仔猪金额（元）17"),
//("出售25公斤以下仔猪重量18", "出售25公斤以下仔猪重量（公斤）18"),

( "肉牛02", "肉牛存栏数02"),
( "能繁殖母牛03", "能繁殖母牛存栏数03"),
( "奶牛04", "奶牛存栏数04"),
( "在产奶牛05", "在产奶牛存栏数05"),
("自繁07", "自繁头数07"),
( "肉牛08", "自繁肉牛头数08"),
( "奶牛09", "自繁奶牛头数09"),
( "购进10", "购进头数10"),
( "肉牛11", "购进肉牛头数11"),
("出售肉牛金额16", "出售肉牛金额（元）16"),
( "出售肉牛重量17", "出售肉牛重量（公斤）17"),
("当季生牛奶产量18", "当季生牛奶产量（公斤）18"),


//( "山   羊02", "山羊存栏只数02"),
//("能繁殖母山羊03", "能繁殖母山羊存栏只数03"),
//( "绵(湖)羊04", "绵(湖)羊存栏只数04"),
//( "能繁殖母绵羊05", "能繁殖母绵羊存栏只数05"),
//( "自繁07", "自繁只数07"),
//( "山   羊08", "山羊自繁只数08"),
//( "绵(湖)羊09", "绵(湖)羊自繁只数09"),
//( "购进10", "购进只数10"),
//( "山   羊11", "山羊购进只数11"),
//( "绵(湖)羊12", "绵(湖)羊购进只数12"),
//( "出售羊金额16", "出售羊金额（元）16"),
//( "出售羊重量17", "出售羊重量（公斤）17"),
//( "当季绵羊毛产量18", "当季绵羊毛产量（公斤）18"),
//( "当季山羊绒产量19", "当季山羊绒产量（公斤）19")

//("肉用02","肉用家禽存栏只数02"),
//("肉鸡03","肉鸡存栏只数03"),
//("蛋用04","蛋用家禽存栏只数04"),
//("蛋鸡05","蛋鸡存栏只数05"),
//("活鸡08","自宰活鸡数量08"),
//("活鸡10","出售活鸡数量10"),
//("出售家禽金额11","出售家禽金额（元）11"),
//("活鸡12","出售活鸡金额（元）12"),
//("出售家禽重量13","出售家禽重量（公斤）13"),
//("活鸡14","出售活鸡重量（公斤）14"),
//("当季禽蛋产量15","当季禽蛋产量（公斤）15"),
//("鸡蛋16","鸡蛋产量（公斤）16"),

("存栏头数（含未断奶小猪）01","生猪存栏头数（含未断奶小猪）01"),
("能繁殖的母猪02","能繁殖的母猪存栏头数02"),
("良种及改良种乳牛05","良种及改良种乳牛存栏头数05"),
("猪  肉14","猪肉产量（吨）14"),
("牛  肉15","牛肉产量（吨）15"),
("羊  肉16","羊肉产量（吨）16"),
("禽  肉17","禽肉产量（吨）17"),
("兔  肉18","兔肉产量（吨）18"),
("其  它19","其他肉产量（吨）19"),
("鸡鸭鹅蛋产量20","鸡鸭鹅蛋产量（吨）20"),
("生牛奶产量21","生牛奶产量（吨）21"),
("蚕茧产量22","蚕茧产量（吨）22"),
("鸡存栏23","鸡存栏只数23"),
("鸡出栏24","鸡出栏只数24"),
            };

          
         
            using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        foreach (var (oldText, newText) in replacements)
                        {
                            string updateQuery = $"UPDATE 审核公式A406 SET sql语句 = REPLACE(sql语句, '{oldText}', '{newText}')";

                            using (var command = new SqliteCommand(updateQuery, connection, transaction))
                            {
                                // 执行更新操作
                                int rowsAffected = command.ExecuteNonQuery();
                                Console.WriteLine($"Updated {rowsAffected} rows with query: {updateQuery}");
                            }
                        }

                        // 提交事务
                        transaction.Commit();
                     await  MyMessagebox.ShowAsyncInMainWindow("修改完成", Icon.Info);
                    }
                    catch (Exception ex)
                    {
                        // 如果发生错误，回滚事务
                        transaction.Rollback();
                        Console.WriteLine($"Error occurred: {ex.Message}");
                    }
                }
            }
        }
    }
}
